﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace Sami_SMS_CRM_Business
{
    public class AdvertismentRepository : Repository
    {
        public IList<crm_advertisment> GetAll()
        {
            return DB.crm_advertisments.OrderBy(p => p.created_on).ToList();
        }

        public DataTable GetAll(int currentPage, int pageSize, ref int totalRecord)
        {
            //var retValue = DB.crm_advertisments.OrderByDescending(p => p.created_on);
            //totalRecord = retValue.Count();
            //var ret = retValue.Skip((currentPage - 1) * pageSize).Take(pageSize);
            //return ret;

            DataTable dt = new DataTable();
            string query = "SELECT [t1].[advertisment_id], [t1].[message], [t1].[sender_name], \n" +
                            "[t1].[mt_flag], [t1].[created_on], [t1].[create_by],  \n" +
                            "[t1].[start_expected], [t1].[end_expected],  \n" +
                            "[t1].[allow_sent_from], [t1].[allow_sent_to],  \n" +
                            "[t1].[action_id], [t1].[collection_id],  \n" +
                            "[t1].[status], [t1].[max_advertisment],  \n" +
                            "[t1].[sent_advertisment], \n" +
                            "(SELECT COUNT(*) FROM mt_queue mq WHERE mq.advertisment_id = [t1].advertisment_id  \n" +
                            "AND cast(mq.date_time AS DATE) = CAST(GETDATE() AS DATE) \n" +
                            "AND mq.[status] = '" + Settings.Status.SENT_GATEWAY + "') [advsent] \n" +
                            "FROM ( \n" +
                            "    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[created_on] DESC) AS [ROW_NUMBER], [t0].* \n" +
                            "    FROM [dbo].[crm_advertisment] AS [t0] \n" +
                            "    ) AS [t1] \n" +
                            "WHERE [t1].[ROW_NUMBER] BETWEEN @p0 AND @p1 \n" +
                            "ORDER BY [t1].[ROW_NUMBER]";
            using (SqlConnection conn = new SqlConnection(Settings.LocalSettings.ConnectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@p0", (currentPage - 1) * pageSize + 1);
                    cmd.Parameters.AddWithValue("@p1", currentPage * pageSize);
                    dt.Load(cmd.ExecuteReader());
                }
            }
            return dt;
        }

        public crm_advertisment GetById(int Id)
        {
            return DB.crm_advertisments.FirstOrDefault(p => p.advertisment_id == Id);
        }

        public IList<crm_advertisment> GetByExpired()
        {
            return DB.crm_advertisments.Where(p => p.end_expected < DateTime.Now).ToList();
        }

        public void DeleleById(int Id)
        {
            var entity = DB.crm_advertisments.FirstOrDefault(p => p.advertisment_id == Id);
            if (entity != null)
            {
                DB.crm_advertisments.DeleteOnSubmit(entity);
                DB.SubmitChanges();
                DB.Refresh(System.Data.Linq.RefreshMode.OverwriteCurrentValues, DB.crm_advertisments);
            }
        }

        public void AddNew(crm_advertisment entity)
        {
            DB.crm_advertisments.InsertOnSubmit(entity);
            DB.SubmitChanges();
            DB.Refresh(System.Data.Linq.RefreshMode.OverwriteCurrentValues, DB.crm_advertisments);
        }

        public void Update(crm_advertisment entity)
        {
            DB.SubmitChanges();
            DB.Refresh(System.Data.Linq.RefreshMode.OverwriteCurrentValues, DB.crm_advertisments);
        }

        public int GetTotalMtQueue(int advertisment_id, int collection_id)
        {
            return DB.mt_queues.Count(p => p.advertisment_id == advertisment_id && p.collection_id == collection_id);
        }

        public string[] GetTotalMtQueue(int advertisment_id)
        {
            DataTable dt = new DataTable();
            string query = "SELECT adv.max_advertisment, adv.sent_advertisment, \n" +
                           " (SELECT COUNT(*) FROM mt_queue mq WHERE mq.advertisment_id = @advertisment_id) sent_in_mt, \n" +
                           " (SELECT COUNT(*) FROM mt_queue mq WHERE mq.advertisment_id = @advertisment_id AND DATEDIFF(dd, mq.date_time, GETDATE()) = 0) sent_in_mt_today \n" +
                           " FROM crm_advertisment adv  \n" +
                           " WHERE advertisment_id = @advertisment_id";
            using (SqlConnection conn = new SqlConnection(Settings.LocalSettings.ConnectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@advertisment_id", advertisment_id);
                    dt.Load(cmd.ExecuteReader());
                }
            }
            if (dt.Rows.Count > 0)
                return new string[] { 
                    dt.Rows[0]["max_advertisment"].ToString(),
                    dt.Rows[0]["sent_advertisment"].ToString(),
                    dt.Rows[0]["sent_in_mt"].ToString(),
                    dt.Rows[0]["sent_in_mt_today"].ToString()
                };
            return null;
        }

        public DataTable StatisticMessage(int id, DateTime? timeFrom, DateTime? timeTo, int currengPage, int pageSize, ref int totalRecord, ref int totalMsg)
        {
            DataTable dt = new DataTable();
            string query = string.Format(
                "DECLARE @tmp TABLE (\n" +
                "    RowNumber INT,\n" +
                "    date_time DATE,\n" +
                "    advsent INT	\n" +
                ")\n" +
                "INSERT INTO @tmp\n" +
                "SELECT ROW_NUMBER() OVER (ORDER BY CAST(mq.date_time AS DATE) DESC), \n" +
                "CAST(mq.date_time AS DATE) date_time, \n" +
                "COUNT(*) advsent \n" +
                "FROM mt_queue mq \n" +
                "WHERE CAST(mq.date_time AS DATE) >= CAST(ISNULL(@timeFrom,mq.date_time) AS DATE) AND  \n" +
                "      CAST(mq.date_time AS DATE) <= CAST(ISNULL(@timeTo,mq.date_time) AS DATE) AND\n" +
                "      mq.advertisment_id = @Id AND\n" +
                "      mq.[status] = '{0}' \n" +
                "GROUP BY CAST(mq.date_time AS DATE) \n" +
                "SELECT RowNumber, date_time, advsent FROM @tmp T\n" +
                "WHERE t.RowNumber BETWEEN {1} AND {2}\n" +
                "ORDER BY T.RowNumber\n" +
                "SELECT @output = COUNT(*), @total =  ISNULL(SUM(advsent),0) FROM @tmp\n",
                Settings.Status.SENT_GATEWAY, (currengPage - 1) * pageSize + 1, currengPage * pageSize);
            using (SqlConnection conn = new SqlConnection(Settings.LocalSettings.ConnectionString))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@Id", id);
                    cmd.Parameters.AddWithValue("@timeFrom", timeFrom ?? (object)DBNull.Value);
                    cmd.Parameters.AddWithValue("@timeTo", timeTo ?? (object)DBNull.Value);
                    cmd.Parameters.Add("@output", SqlDbType.Int).Direction = ParameterDirection.Output;
                    cmd.Parameters.Add("@total", SqlDbType.Int).Direction = ParameterDirection.Output;
                    dt.Load(cmd.ExecuteReader());
                    totalRecord = (int)cmd.Parameters["@output"].Value;
                    totalMsg = (int)cmd.Parameters["@total"].Value;
                }
            }
            return dt;
        }
    }
}
